﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using DevExpress.ExpressApp;

namespace SMSTester
{
    public class SQLConnect
    {
        public static SQLConnect Instance = new SQLConnect();

        public  SqlConnection GetConnection(string paramConnectionString)
        {
            SqlConnection connection =
                new SqlConnection(paramConnectionString);
            return connection;
        }

        public  DataTable Select(
            SqlConnection paramConnection, 
            string paramQuery,
            List<KeyValuePair<string,Type>> paramColumns
            )
        {
            SqlCommand cmd = paramConnection.CreateCommand();
            paramConnection.Open();

            cmd.CommandText = paramQuery;
            cmd.CommandType = CommandType.Text;
            DataTable locResult = new DataTable();
            foreach(KeyValuePair<string,Type> obj in paramColumns)
                locResult.Columns.Add(obj.Key, obj.Value);

            SqlDataReader reader = cmd.ExecuteReader();
            locResult.Load(reader);
            reader.Close();
            paramConnection.Close();

            return locResult;
        }

        public  DataTable Select(
            string paramQuery, 
            List<KeyValuePair<string, Type>> paramColumns)
        {
            return Select(GetConnection(), paramQuery, paramColumns);
        }

        public  int ExecuteNonQuery( 
            SqlConnection paramConnection,
            string paramQuery
            )
        {
            SqlCommand cmd = paramConnection.CreateCommand();
            paramConnection.Open();

            cmd.CommandText = paramQuery;
            cmd.CommandType = CommandType.Text;
            int locResult = cmd.ExecuteNonQuery();
            paramConnection.Close();

            return locResult;

        }

        public  int ExecuteNonQuery(
            string paramQuery)
        {
            return ExecuteNonQuery(GetConnection(), paramQuery);
        }

        public int ExecuteNonQueryReturnIdentity(
            SqlConnection paramConnection,
            string paramQuery
            )
        {
            SqlCommand cmd = paramConnection.CreateCommand();
            paramConnection.Open();

            cmd.CommandText = paramQuery;
            cmd.CommandType = CommandType.Text;
            int locResult = (int)cmd.ExecuteScalar();

            paramConnection.Close();

            return locResult;

        }

        public int ExecuteNonQueryReturnIdentity(
            string paramQuery)
        {
            return ExecuteNonQueryReturnIdentity(GetConnection(), paramQuery);
        }


    }
}
